function generateSQL(val) {
    Console.log(val);
    if (val === 1) {
        return generateMenuSql();
    }
}

function generateSysSql() {
    let delStr = "delete from sys_m_module where module_sid = " + buildStr('B', true);
    let insertMModuleStr = insertMModule();

    let delRoleModuleStr = "DELETE FROM sys_m_role_module WHERE role_sid = '301' and module_sid = " + buildStr('B', true);
    let insertRoleModuleStr = "INSERT INTO sys_m_role_module (role_sid, module_sid) VALUE ('301', " + buildStr3('B', true);

    let delRoleModuleDefaultStr = "DELETE FROM sys_m_role_module_default WHERE role_sid = '301' and module_sid = " + buildStr('B', true);
    let insertRoleModuleDefaultStr = "INSERT INTO sys_m_role_module_default (role_sid, module_sid) VALUE ('301', " + buildStr3('B', true);
    return delStr + insertMModuleStr + delRoleModuleStr + insertRoleModuleStr + delRoleModuleDefaultStr + insertRoleModuleDefaultStr;
}

function insertMModule() {
    let str = "INSERT INTO sys_m_module (module_sid, module_name, module_url, module_icon_url, parent_sid, permission,module_type, display_flag, sort_rank, module_category, description)";
    let values = " VALUES (";
    values += buildSysStr('B');
    values += buildSysStr('C');
    values += buildSysStr('D');
    values += buildSysStr('E');
    values += buildSysStr('F');
    values += buildSysStr('G');
    values += buildSysStr('H', true);
    values += buildSysStr('I', true);
    values += buildSysStr('J', true);
    values += buildSysStr('K');
    values += "''";
    values += ");";
    return str + values;
}

function buildSysStr(str, isNum) {
    let orgS = str;
    let row = ActiveCell.Row;
    str += row;
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL" || ss === '') {
        s = "''";
    }
    if (isNum) {
        s = ss;
    }
    s += ",";
    return s;
}

function generateMenuSql(row) {
    return generateMenuSqlRole("302", row);
}

function generateMenuSystemSql(row) {
    return generateMenuSqlRole("100", row);
}

function generateMenuAdminSql(row) {
    return generateMenuSqlRole("301", row);
}

function generateMenuSqlRole(RoleId, row) {
    let delStr = "DELETE FROM sys_auth_module WHERE auth_key = " + buildStrRow('B' + row, true);
    let insertStr = buildInsert(row);

    let defaultGroupId = "1";
    let delGroupStr = "DELETE FROM sys_group_module_rela WHERE group_sid = " + defaultGroupId + " AND auth_key = " + buildStrRow('B' + row, true);
    let insertGroupStr = insertGroup(defaultGroupId, row);

    let defatuleRoleId = RoleId;
    let delRoleStr = "DELETE FROM sys_role_module_rela WHERE role_id = " + defatuleRoleId + " AND auth_key = " + buildStrRow('B' + row, true);
    let insertRoleStr = insertRole(defatuleRoleId, row);
    return delStr + insertStr + delGroupStr + insertGroupStr + delRoleStr + insertRoleStr;
}

function insertRole(defatuleRoleId, row) {
    let str = "INSERT INTO sys_role_module_rela (role_id, auth_key, rela_type, parent_module, critical,created_by, created_dt, updated_by, updated_dt, version,auth_type)";
    let values = " VALUES (";
    values += buildEmpty(defatuleRoleId);
    values += buildStr2Row('B' + row, false);
    values += buildEmpty("module", true);
    values += buildStr2Row('E' + row, false);
    values += buildEmpty("0");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("1", false);
    values += "NULL";
    values += ");";
    return str + values;
}

function insertGroup(defaultGroupId, row) {
    let str = "INSERT INTO sys_group_module_rela (group_sid, auth_key, rela_type, parent_module, critical,created_by, created_dt, updated_by, updated_dt, version,auth_type)";
    let values = " VALUES (";
    values += buildEmpty(defaultGroupId);
    values += buildStr2Row('B' + row, false);
    values += buildEmpty("module", true);
    values += buildStr2Row('E' + row, false);
    values += buildEmpty("0");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("1", false);
    values += "'whiteList'";
    values += ");";
    return str + values;
}

function buildInsert(row) {
    let str = "INSERT INTO sys_auth_module (auth_key , name, name_en, parent_auth_key, icon, module_type,request_type, request_url, module_url, display_type, component,dashboard_id, resource_column_id, sort_rank, status, description,description_en, created_by, created_dt, updated_by, updated_dt,version, display_location, shortcut_name, collapse_menu, router_query, display_status, service_directory_id, console_link, module_category)";
    let values = " VALUES (";
    values += buildStrRow('B' + row, false);
    values += buildStrRow('C' + row, false);
    values += buildStrRow('D' + row, false);
    values += buildStrRow('E' + row, false);
    values += buildStrRow('F' + row, false);
    values += buildStrRow('G' + row, false);
    values += buildStrRow('H' + row, false);
    values += buildStrRow('I' + row, false);
    values += buildStrRow('J' + row, false);
    values += buildStrRow('K' + row, false);
    values += buildStrRow('L' + row, false);
    values += buildEmpty("");
    values += buildStrRow('R' + row, false);

    values += buildStrRow('M' + row, false);
    values += buildStrRow('N' + row, false);
    values += buildEmpty("null", false);
    values += buildEmpty("null", false);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("1", false);
    values += buildEmpty("sidebar", true);
    values += buildEmpty("null", false);
    values += buildEmpty("null", false);

    values += buildStrRow('O' + row, false);
    values += buildStrRow('P' + row, false);
    values += buildEmpty("");
    values += buildEmpty("");

    values += buildStrRow('Q' + row);
    values += ");";
    return str + values;
}

function buildEmpty(str, b) {
    let s = "";
    if (str === "") {
        s = "''";
    } else if (str === "null" || str === "NULL") {
        s = "NULL";
    } else {
        s = str;
    }
    if (b) {
        s = "'" + s + "'";
    }
    return s + ",";
}

function buildStr3(str, end) {
    let orgS = str;
    let row = ActiveCell.Row;
    str += row;
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL" || ss === '') {
        s = "''";
    }
    if (end) {
        s += ");";
    } else {
        s += ",";
    }
    return s;
}

function buildStr3Row(str, end) {
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL" || ss === '') {
        s = "''";
    }
    if (end) {
        s += ");";
    } else {
        s += ",";
    }
    return s;
}

function buildStr(str, end) {
    let orgS = str;
    let row = ActiveCell.Row;
    str += row;
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL" || ss === '') {
        s = "''";
    }
    if (orgS === "M") {
        s = ss;
    }
    if (orgS === "R") {
        if (ss === '') {
            s = "NULL";
        } else {
            s = ss;
        }
    }

    if (orgS === "Q") {
        return s;
    }
    if (end) {
        s += ";";
    } else {
        s += ",";
    }
    return s;
}

function buildStrRow(str, end) {
    let orgS = str.substring(0, 1);
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL" || ss === '') {
        s = "''";
    }
    if (orgS === "M") {
        s = ss;
    }
    if (orgS === "R") {
        if (ss === '') {
            s = "NULL";
        } else {
            s = ss;
        }
    }

    if (orgS === "Q") {
        return s;
    }
    if (end) {
        s += ";";
    } else {
        s += ",";
    }
    return s;
}


function buildStr2Row(str, end) {
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL") {
        s = "''";
    }

    if (end) {
        return s;
    } else {
        s += ",";
    }
    return s;
}

function buildStr2(str, end) {
    let row = ActiveCell.Row;
    str += row;
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (ss === "null" || ss === "NULL") {
        s = "''";
    }

    if (end) {
        return s;
    } else {
        s += ",";
    }
    return s;
}

function getRoleModuleRelaStr(roleId, row) {
    let str = "INSERT INTO sys_role_module_rela (role_id, auth_key, rela_type, parent_module, critical, created_by,created_dt, updated_by, updated_dt, version, auth_type)";
    let values = " VALUES (";
    values += roleId + ",";
    values += buildStr2Row('C' + row);
    values += "'action',";
    values += buildStr2Row('B' + row);
    values += buildEmpty("0");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp", false);
    values += buildEmpty("1", false);
    values += buildStr3Row('D' + row, true);
    return str + values;
}

function getText(s) {
    return Range(s).Text;
}

function generateActionSql(row) {
    let delAuthStr = "delete from sys_auth_action where auth_key =" + buildStrRow('C' + row, true);
    let insAuthStr = insAuth(row);
    //interFaceId = 19981415345353500,19981415345353501,19981415345353502 处理单权限多接口
    let interFaceId = Range("K" + row).Text;
    let delActionUrlStr = "";
    let insActionUrlRealStr = "";
    if (interFaceId !== '') {
        //分割接口ID
        let interfaceIds = interFaceId.split(",");
        delActionUrlStr = "delete from sys_action_url_rela where interface_url_id in (" + interFaceId + ") and action_auth_key = " + buildStrRow('C' + row, true);
        insActionUrlRealStr = "INSERT INTO sys_action_url_rela (interface_url_id, action_auth_key, created_by, created_dt, updated_by,updated_dt, version) VALUES";
        //遍历接口ID
        for (let i = 0; i < interfaceIds.length; i++) {
            let interfaceId = interfaceIds[i];
            insActionUrlRealStr += insActionUrlReal(interfaceId, row);
            if (i < interfaceIds.length - 1) {
                insActionUrlRealStr += ",";
            }
        }
        insActionUrlRealStr += ";";
    }

    let moduleKeys = Range("B" + row).Text;
    let moduleKey = "'" + moduleKeys + "'";
    let delModuleActionRelaStr = "delete FROM sys_module_action_rela WHERE module_auth_key = " + moduleKey + " and action_auth_key = " + buildStrRow('C' + row, true);
    let insModuleActionRelsStr = insModuleActionRels(row);

    let delGroupModuleRelaStr = "delete from sys_group_module_rela where group_sid = 1 and rela_type = 'action' and parent_module = " + moduleKey + " and auth_key = " + buildStrRow('C' + row, true);
    let insGroupModuleRelaStr = insGroupModuleRela(row);

    // 权限关联角色
    // 多个角色
    let roleIds = getText("L" + row);
    let roleModuleRelaStr = "";
    if (roleIds !== '') {
        let roleIdsArr = roleIds.split(",");
        for (let i = 0; i < roleIdsArr.length; i++) {
            let roleId = roleIdsArr[i];
            roleModuleRelaStr += "delete from sys_role_module_rela where role_id = " + roleId + " and rela_type = 'action' and auth_type = '"
                + getText("D" + row) + "' and parent_module = " + moduleKey + " and auth_key = " + buildStrRow('C' + row, true);
            roleModuleRelaStr += getRoleModuleRelaStr(roleId, row);
        }
    }

    return delAuthStr + insAuthStr + delActionUrlStr + insActionUrlRealStr + delModuleActionRelaStr + insModuleActionRelsStr + delGroupModuleRelaStr + insGroupModuleRelaStr + roleModuleRelaStr;
}

function insGroupModuleRela(row) {
    let str = "INSERT INTO sys_group_module_rela (group_sid, auth_key, rela_type, parent_module, critical, created_by,created_dt, updated_by, updated_dt, version, auth_type)";
    let values = " VALUES (";
    values += buildEmpty("1");
    values += buildStr3Row("C" + row);
    values += buildEmpty("action", true);
    values += buildStr3Row("B" + row);
    values += "0,";
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += "1,";
    values += "'whiteList'";
    values += ");";
    return str + values;
}

function insModuleActionRels(row) {
    let str = "INSERT INTO sys_module_action_rela (module_auth_key, action_auth_key, created_by, created_dt, updated_by,updated_dt, version)";
    let values = " VALUES (";
    values += buildStr3Row("B" + row);
    values += buildStr3Row("C" + row);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += "1";
    values += ");";
    return str + values;
}

function insActionUrlReal(interFaceId, row) {
    let values = "  (";
    values += interFaceId + ",";
    values += buildStr3Row("C" + row);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += "1";
    values += ")";
    return values;
}

function insAuth(row) {
    let str = "INSERT INTO sys_auth_action (auth_key, auth_type, name, name_en, parent_auth_key, service_code,description, description_en, created_by, created_dt, updated_by,updated_dt, version)";
    let values = " VALUES (";
    values += buildStr3Row("C" + row);
    values += buildStr3Row("D" + row);
    values += buildStr3Row("E" + row);
    values += buildStr3Row("F" + row);
    values += buildStr3Row("G" + row);
    values += buildStr3Row("H" + row);
    values += buildStr3Row("I" + row);
    values += buildStr3Row("J" + row);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += "1";
    values += ");";
    return str + values;
}

function isNull(strTxt) {
    return strTxt === "null" || strTxt === "NULL" || strTxt === '';
}

function buildId(str) {
    // 获取当前时间戳
    const timestamp = Date.now();
    // 生成一个随机数
    const randomNum = Math.floor(Math.random() * 1000);
    // 获取指定单元格的文本
    let strTxt = Range(str).Text;

    // 检查单元格内容
    if (isNull(strTxt)) {
        // 返回唯一的数字ID
        return timestamp + randomNum;
    }

    // 返回单元格的文本
    return parseInt(strTxt, 10);
}

function buildStr4(str) {
    let orgS = str;
    let ss = Range(str).Text;
    let s = "'" + ss + "'";
    if (isNull(ss)) {
        s = "''";
    }
    return s += ",";
}

function insertInterfaceSQL(row) {
    let id = buildId("A" + row)
    let str = deleteInterfaceSQL(id) + " " + "INSERT INTO sys_interface_url (id, module_url_name, services_code, module_url_type, url_method, module_url,sensitive_flag, created_by, created_dt, updated_by, updated_dt, version)";

    let values = " VALUES (";

    values += id + ",";
    values += buildStr4("B" + row);
    values += buildStr4("E" + row);
    values += buildStr4("F" + row);
    values += buildStr4("C" + row);
    values += buildStr4("D" + row);
    values += buildStr4("G" + row);
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += buildEmpty("admin", true);
    values += buildEmpty("current_timestamp");
    values += "1";
    values += ");";
    return str + values;
}

function deleteInterfaceSQL(id) {
    return "DELETE FROM sys_interface_url WHERE id =" + id + ";";
}

/**
 * Macro1 Macro
 * 宏由 13452 录制，时间: 2024/09/25
 */
function Macro1() {
    for (let i = 0; i < 1000; i++) {
        let j = i + 2;
        let r = "B" + j;
        let ss = Range(r).Text;
        if (ss === '' || ss === "null" || ss === "NULL") {
            console.log("一共" + (j - 1) + "行");
            break;
        }
        let rr = "S" + j;
        Selection.Formula = "=generateSQL(1)";
        Range(rr).Select();
    }
}


function generateInterfacePermissions() {

    // 获取当前sheet
    const ActiveSheet = Application.ActiveWorkbook.ActiveSheet;
    //判断名称是否为"interface"
    if (ActiveSheet.Name !== "interface") {
        return;
    }
    //结果的列
    const currentColumn = "H";
    // 遍历当前列的所有单元格
    for (let row = 2; row <= 10000; row++) {
        let value = ActiveSheet.Cells(row, "A").Formula;
        if (isNull(value)) {
            break;
        }
        ActiveSheet.Cells(row, currentColumn).Formula = insertInterfaceSQL(row);
    }

}


function generateModulePermissions() {
    // 获取当前sheet
    const ActiveSheet = Application.ActiveWorkbook.ActiveSheet;
    //判断名称是否为"action"
    if (ActiveSheet.Name !== "action") {
        return;
    }
    //结果的列
    const currentColumn = "M";
    // 遍历当前列的所有单元格
    for (let row = 2; row <= 10000; row++) {
        let value = ActiveSheet.Cells(row, "B").Formula;
        // 如果单元格内容为空，则退出循环
        if (value === '' || value === "null" || value === "NULL") {
            break;
        }
        ActiveSheet.Cells(row, currentColumn).Formula = generateActionSql(row);
    }
}


function generateMenuPermissions() {
    // 获取当前sheet
    const ActiveSheet = Application.ActiveWorkbook.ActiveSheet;
    //判断名称是否为"租户首页1"
    if (ActiveSheet.Name !== "租户首页1" && ActiveSheet.Name !== "资源控制台") {
        return;
    }
    //结果的列
    const currentColumn = "S";
    // 遍历当前列的所有单元格
    for (let row = 2; row <= 10000; row++) {
        let value = ActiveSheet.Cells(row, "B").Formula;
        // 如果单元格内容为空，则退出循环
        if (value === '' || value === "null" || value === "NULL") {
            break;
        }
        ActiveSheet.Cells(row, currentColumn).Formula = generateMenuSql(row);
    }
}

function generateMenuSystemPermissions() {
    // 获取当前sheet
    const ActiveSheet = Application.ActiveWorkbook.ActiveSheet;
    //判断名称是否为"系统控制台"
    if (ActiveSheet.Name !== "系统控制台") {
        return;
    }
    //结果的列
    const currentColumn = "S";
    // 遍历当前列的所有单元格
    for (let row = 2; row <= 10000; row++) {
        let value = ActiveSheet.Cells(row, "B").Formula;
        // 如果单元格内容为空，则退出循环
        if (value === '' || value === "null" || value === "NULL") {
            break;
        }
        ActiveSheet.Cells(row, currentColumn).Formula = generateMenuSystemSql(row);
    }
}

function generateMenuAdminPermissions() {
    // 获取当前sheet
    const ActiveSheet = Application.ActiveWorkbook.ActiveSheet;
    //判断名称是否为"运营控制台"
    if (ActiveSheet.Name !== "运营控制台") {
        return;
    }
    //结果的列
    const currentColumn = "S";
    // 遍历当前列的所有单元格
    for (let row = 2; row <= 10000; row++) {
        let value = ActiveSheet.Cells(row, "B").Formula;
        // 如果单元格内容为空，则退出循环
        if (value === '' || value === "null" || value === "NULL") {
            break;
        }
        ActiveSheet.Cells(row, currentColumn).Formula = generateMenuAdminSql(row);
    }
}

function Workbook_BeforeSave(SaveAsUI, Cancel) {
    // 自动生成接口权限
    generateInterfacePermissions()
    // 自动生成模块权限
    generateModulePermissions()
    // 自动生成菜单权限
    generateMenuPermissions()
    // 自动生成系统菜单
    generateMenuSystemPermissions()
    // 自动生成管理员菜单
    generateMenuAdminPermissions()
    //保存结束
    MsgBox("SQL生成完成,保存成功！")

}

function Workbook_Open() {
    MsgBox("不要替换文件，防止公式遗漏！")
    // 保存可以自动生成权限
    MsgBox("Ctrl+S 保存文件，保存后自动生成当前sheet的SQL！")
}
